Como usar a fun莽茫o COALESCE() em SQL

您所在的位置：网站首页 › plsql coalesce › Como usar a fun莽茫o COALESCE() em SQL

Como usar a fun莽茫o COALESCE() em SQL

2024-07-13 22:44| 来源: 网络整理| 查看: 265

Os usu谩rios SQL s茫o freq眉entemente confrontados com valores NULL em suas consultas e precisam process谩-los corretamente. A fun莽茫o COALESCE() ajuda a lidar com os valores NULL. Leia este artigo para aprender como usar COALESCE() em suas consultas.

As tabelas SQL armazenam dados em registros, e os registros s茫o compostos de campos. Pode haver situa莽玫es em que n茫o sabemos o valor para um campo espec铆fico. Por exemplo, vamos supor que temos uma tabela com dados para persons. Tem os campos first_name, last_name, e marital_status. Quando n茫o conhecemos o marital_status para uma determinada pessoa, SQL nos permite atribuir um valor NULL para este campo. Entretanto, o valor NULL n茫o significa que a pessoa n茫o tem um marital_status; significa apenas "n茫o conhecemos esse valor".

Em outras palavras, SQL usa valores NULL para representar a aus锚ncia de valor. Entretanto, os valores NULL podem ser complicados de manusear. 脡 por isso que SQL incluiu a fun莽茫o COALESCE(), que 茅 o que vamos falar neste artigo.

Antes de come莽ar os detalhes t茅cnicos do NULL e COALESCE(), sugiro verificar nosso curso interativo em Fun莽玫es Comuns em SQL. Ele cont茅m uma revis茫o abrangente dos valores NULL, fun莽玫es relacionadas ao NULL, e outras fun莽玫es comuns em SQL.

O que faz a COALESCE()?

Em bancos de dados SQL, qualquer tipo de dado admite NULL como um valor v谩lido; ou seja, qualquer coluna pode ter um valor NULL, independentemente do tipo de dado que seja. (Obviamente, algumas colunas ser茫o obrigat贸rias (n茫o-nul谩veis), mas isto 茅 definido pelo projetista do banco de dados, n茫o o tipo de dados em si). Vamos mostrar um exemplo simples usando a tabela persons.

first_namelast_namemarital_status CharlesLeclercsingle FernandoAlonsomarried GeorgeGraueNULL

Podemos usar a fun莽茫o SQL COALESCE() para substituir o valor NULL por um texto simples:

SELECT first_name, last_name, COALESCE(marital_status,'Unknown') FROM persons

Na consulta acima, a fun莽茫o COALESCE() 茅 utilizada para retornar o valor 'Unknown' somente quando marital_status 茅 NULL. Quando marital_status n茫o 茅 NULL, COALESCE() retorna o valor da coluna marital_status. Em outras palavras, COALESCE() retorna o primeiro argumento n茫o NULL.

Conhe莽a os dados do exemplo

Para o restante do artigo, demonstraremos a fun莽茫o COALESCE() utilizando a tabela stockque 茅 mostrado abaixo.

productbrandsubcategorycategoryfamilyunitsquantity_availableminimum_to_have pork ribsNULLpork meatmeatfoodKilos400130 tomatoesMr RedNULLvegetablesfoodKilos280100 lettuceNULLLeaf vegetablesNULLfoodKilos280125 bananasBig BrasilNULLvegetablesfoodKilos450150 hamburgerMaxBurgcow meatmeatfoodBox245100 hamburgerRoyalBurgcow meatmeatfoodBox125NULL hamburgerSuperBurgaNULLNULLNULLBox20080

Esta tabela armazena registros de dados do produto para um mercado e inclui as colunas product, brand, subcategory, category, family, units, quantity_available (o estoque atual deste produto), e minimum_to_have (o limiar quando o mercado precisa encomendar este produto de seus fornecedores).

Voc锚 notar谩 que alguns dos produtos t锚m uma subcategoria, mas outros n茫o. Por exemplo, o produto "costelas de porco" pertence 脿 subcategoria "carne de porco" na categoria "carne" e a fam铆lia "alimento". O produto "tomate" pertence 脿 categoria "vegetais" e a fam铆lia "alimento"; n茫o tem uma subcategoria, portanto existe um NULL neste campo.

Exemplo de Consultas SQL usando a fun莽茫o COALESCE()

Agora, vamos ver como usar a fun莽茫o COALESCE() em alguns exemplos realistas.

Exemplo 1: Use COALESCE() para Substituir NULL por um R贸tulo

Queremos mostrar todos os produtos com sua subcategoria, categoria e fam铆lia. Entretanto, h谩 alguns produtos com um NULL em sua categoria ou subcategoria. Para estes produtos, queremos exibir um texto: 'No Category' ou 'No Subcategory'. Aqui est谩 a consulta que usar铆amos:

SELECT product, COALESCE(subcategory,'No Subcategory') AS subcategory, COALESCE(category,'No Category') AS category, COALESCE(family,'No Family') AS family FROM stock

Estamos usando a fun莽茫o COALESCE() para substituir os valores NULL por um texto. Voc锚 pode ver o resultado abaixo:

productsubcategorycategoryfamily pork ribspork meatmeatfood tomatoesNo Subcategoryvegetablesfood lettuceLeaf vegetablesNo Categoryfood bananasNo Subcategoryvegetablesfood hamburgercow meatmeatfood hamburgercow meatmeatfood hamburgerNo SubcategoryNo CategoryNo Family Exemplo 2: Use COALESCE() ao concatenar NULL e Strings

Um problema SQL frequente relacionado com os valores NULL 茅 a concatena莽茫o de cordas. Muitas opera莽玫es envolvendo valores NULL retornam um valor NULL como resultado. Se quisermos concatenar duas cordas e uma delas for NULL, o resultado da concatena莽茫o ser谩 NULL. Aqui est谩 uma simples concatena莽茫o de texto:

SELECT 'Hello, how are you ' || 'Peter ' || '?' AS example

Ele retorna:

example Hello, how are you Peter ?

Entretanto, se usarmos um texto NULL ...

SELECT 'Hello, how are you ' || null || '?' AS example

... n贸s conseguimos:

example NULL

O resultado 茅 NULL porque cada concatena莽茫o de texto envolvendo um valor NULL retorna um valor NULL. Para evitar isso, podemos usar a fun莽茫o COALESCE() para retornar uma string vazia (ou um espa莽o) em vez de um valor NULL. Por exemplo, suponha que queremos uma lista dos nomes dos produtos com o nome da marca. Podemos escrever a seguinte consulta:

SELECT product || ', brand: ' || COALESCE(brand, '--') AS product_brand FROM stock

Quando uma marca 茅 NULL, colocaremos um '--' em vez de um NULL. Observe o resultado:

product_brand pork ribs, brand: -- tomatoes, brand: Mr Red lettuce, brand: -- bananas, brand: Big Brazil hamburger, brand: MaxBurg hamburger, brand: RoyalBurg hamburger, brand: SuperBurga Exemplo 3: Use COALESCE() com Argumentos M煤ltiplos

Voc锚 pode usar a fun莽茫o COALESCE() com mais de dois argumentos. Suponhamos que queremos um relat贸rio que liste os produtos e suas subcategorias. Se a subcategoria for NULL, queremos substituir a subcategoria pela categoria. E se ambas subcategoria e categoria s茫o NULL, queremos substitu铆-las pela fam铆lia do produto. Vamos ver a consulta SQL:

SELECT product ||' - '|| COALESCE(subcategory, category, family, 'no product description ') AS product_and_subcategory FROM stock

Estamos usando a fun莽茫o COALESCE() com quatro argumentos; o primeiro argumento n茫o NULL ser谩 retornado, como podemos ver no resultado abaixo:

product_and_subcategory pork ribs - pork meat tomatoes - vegetables lettuce - leaf vegetables Bananas - vegetables hamburger - cow meat hamburger - cow meat hamburger - no product description

Para mais detalhes sobre isto, sugiro o artigo How to Tackle SQL NULLs.

Exemplo 4: Use COALESCE() para substituir NULL por um Valor Calculado

A fun莽茫o SQL COALESCE() tamb茅m pode ser usada para calcular ou estimar um valor quando este valor n茫o est谩 presente. Por exemplo, cada produto tem um limite (representado pela coluna minimum_to_have) que requer um novo pedido ao fornecedor. Entretanto, alguns registros poderiam ter um valor NULL na coluna minimum_to_have; neste caso, podemos definir que o limite ser谩 50% da coluna quantity_available. A consulta para calcular a estimativa do valor limite 茅:

SELECT product, quantity_available, minimum_to_have, COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold FROM stock

A fun莽茫o COALESCE() aqui retorna minimum_to_have quando o valor minimum_to_have n茫o 茅 NULL. Se minimum_to_have for NULL, ent茫o COALESCE() retornar谩 quantity_available * 0.5

productquantity_availableminimum_to_havethreshold pork ribs400130130 tomatoes280NULL140 lettuce280125125 bananas450150150 hamburger245100100 hamburger125100100 hamburger2008080 Exemplo 5: Use COALESCE() com a Cl谩usula ROLLUP

No pr贸ximo exemplo, usaremos a cl谩usula ROLLUP (uma extens茫o de GROUP BY) para obter a quantidade total de produtos que temos para cada subcategoria, incluindo um subtotal de produtos para cada categoria e fam铆lia. Vamos ver a consulta:

SELECT family, category, subcategory, SUM(quantity_available) as quantity_in_stock FROM stock GROUP BY ROLLUP(family, category, subcategory) ORDER BY family, category, subcategory

A cl谩usula ROLLUP assume uma hierarquia entre as colunas family, category, e subcategory. Assim, ela gera todos os conjuntos de agrupamentos que fazem sentido considerando a hierarquia: GROUP BY family, GROUP BY family, category e GROUP BY family, category, subcategory. Esta 茅 a raz茫o pela qual ROLLUP 茅 freq眉entemente utilizada para gerar subtotais e totais gerais para relat贸rios.

Vamos ver os resultados abaixo:

familycategorysubcategoryquantity_in_stock foodmeatcow meat570 foodmeatpork meat400 foodmeatNULL970 foodvegetablesleaf vegetables280 foodvegetablesnon leaf vegetables730 foodvegetablesNULL1010 foodNULLNULL1980 NULLNULLNULL1980

Voc锚 pode ver alguns NULLs no resultado anterior. Cada NULL significa que esta coluna n茫o estava presente no GROUP BY para o c谩lculo da quantidade em estoque. Por exemplo, estas linhas ...

foodmeatNULL770 foodvegetablesNULL1010

... s茫o o resultado da execu莽茫o de GROUP BY family, category. Esta 茅 a raz茫o para ter um NULL sob a coluna subcategory.

Na pr贸xima consulta, usaremos a fun莽茫o COALESCE() para melhorar a legibilidade do relat贸rio. Substituiremos esses valores NULL por um texto que esclarece o motivo do NULL:

SELECT COALESCE(family,'All Families') AS family, COALESCE(category,'All Categories') AS category, COALESCE(subcategory,'All Subcategories') AS subcategory, SUM(quantity_available) as quantity_in_stock FROM stock GROUP BY ROLLUP(family, category, subcategory) ORDER BY family, category, subcategory

O resultado 茅:

familycategorysubcategoryquantity_in_stock foodmeatCow meat570 foodmeatPork meat400 foodmeatAll Sub-Categories970 foodvegetablesLeaf vegetables280 foodvegetablesNon leaf vegetables730 foodvegetablesAll Subcategories1010 foodAll CategoriesAll Subcategories1980 All FamiliesAll CategoriesAll Subcategories1980

No resultado anterior, podemos observar como os valores NULL s茫o substitu铆dos por textos e as linhas dos relat贸rios com os textos come莽ando com 'All' mostram um subtotal em quantity_in_stock.

Gostaria de sugerir o artigo The SQL COALESCE Function: Manuseio dos valores NULL, se voc锚 quiser saber mais sobre isto.

Vantagem de COALESCE() Para processar valores NULL

Neste artigo, demonstramos v谩rias maneiras de usar a fun莽茫o SQL COALESCE(). Cobrimos como usar COALESCE() para substituir valores NULL, como calcular um valor alternativo, e como combinar COALESCE() com a cl谩usula ROLLUP, entre outros exemplos.

Antes de terminar, tenho outra recomenda莽茫o para voc锚. Nossa Folha de Consulta gratuita Fun莽玫es Comuns em SQL permite que voc锚 encontre rapidamente detalhes sobre as fun莽玫es embutidas do SQL, fun莽玫es agregadas e muito mais. Eu a uso quase todos os dias quando trabalho com SQL. Voc锚 tamb茅m pode experimentar nossa Fun莽玫es Comuns em SQL curso, onde voc锚 pode aprender e praticar as fun莽玫es SQL num茅ricas, de texto, data e NULL-handling. Aumente suas habilidades e aumente seus ativos!

【本文地址】

公司简介

联系我们